DATABASE = (function(){ return { // air database
	db: null, dbStatus: false

	,getAll: function(table, sort){ return this.dbTable('SELECT * FROM '+table+(sort?' ORDER BY '+sort:''));	}

	,getByID: function(table,id){ if(!id||!table)return; return this.dbObject('SELECT * FROM '+table+' WHERE id=?',[id]) }

	,countRecords: function(table){ if(!table)return; var ile=this.dbObject('SELECT count(id) as ile FROM '+table); return ile.ile }

	,dbTable: function(sql, vals){
		if (!this.dbStatus) return;
		vals=vals||[];
		var dbQuery = new air.SQLStatement(), records = new Array(), i=vals.length, rs, i=0, dlen;
		dbQuery.sqlConnection = this.db;  
		dbQuery.text = sql;
		if(i) while(--i>=0) dbQuery.parameters[i] = vals[i];
		dbQuery.execute();
		rs = dbQuery.getResult()
		if (!rs.data) return;
		for (i=0, dlen=rs.data.length; i<dlen; i++) records.push(rs.data[i]);	
		return records;
	}
	
	,dbObject: function(sql,vals){
		if (!this.dbStatus) return;
		var dbQuery = new air.SQLStatement(), i=vals.length, rs;
		dbQuery.sqlConnection = this.db;  
		dbQuery.text = sql;  
		if(i) while(--i>=0) dbQuery.parameters[i] = vals[i];
		dbQuery.execute();
		dbQuery.clearParameters();
		rs = dbQuery.getResult()
		if (!rs.data) return;
		return rs.data[0];
	}
	
	,addData: function (table, rec){
		var fields=new Array(), values= new Array(), vals= new Array();
		for (i in rec){ fields.push(i); values.push('?'); vals.push(rec[i]); }
		if (fields.length) return this.dbEdit('insert into '+table+' ('+fields.join()+') values('+values.join()+')', vals) 
	}
	
	,editData: function (table, rec, id){
		var fields=new Array(), vals=new Array();
		if (rec.id) delete(rec.id);
		for (i in rec){ fields.push(' '+i+'=?'); vals.push(rec[i]); }
		vals.push(id);
		if (fields.length) return this.dbEdit('update '+table+' set '+fields.join()+' where id=?', vals) 
	}
	
	,delData: function (table, id){ if (!id || !table) return false; return this.dbEdit('delete from '+table+' where id=?', [id]) }
	
	,dbEdit: function(sql, vals){
		if (!this.dbStatus) return;
		vals=vals||[];
		var dbQuery = new air.SQLStatement(), i, rs;
		dbQuery.sqlConnection = this.db;
		dbQuery.text = sql;
		i=vals.length
		if(i) while(--i>=0) dbQuery.parameters[i] = vals[i];
		dbQuery.execute();
		dbQuery.clearParameters();
		rs = dbQuery.getResult()
		if (rs.lastInsertRowID) return rs.lastInsertRowID;
		return rs.rowsAffected;
	}
	
	
	,dbInit: function(name){
		if (!air || !air.SQLConnection) {
			msgbox({ type:'error', autoClose:false, text: 'Cannot find AIR runtime' });
			return;
		} 
		this.db = new air.SQLConnection();
		if (this.db){ 
			var dbFile = air.File.applicationDirectory.resolvePath(name+'.db');
			try{ this.db.open(dbFile); }   // check openAsync
			catch (e){  
				msgerror("DB error (db init): ", e.message);
				die("Details:", e.details);
				return;  
			}
			this.dbStatus=true;
		}
	}
	
	,resetTable: function(conf){ 
		try{ 
			this.dbEdit('drop table if exists '+conf.tableName);
			this.dbEdit(conf.tableSql); 
			this.dbStatus=true;
		}catch(e){ die('DB ERROR (reset table): , '+e.message)}
	}
	
	,tableInit: function(config){
		if (!this.db || !this.dbStatus) return;
		if (!config.tableName || !config.tableSql) return;
		var defaults = { tableName:'mytable', tableSql:''  }, conf={};
		$.extend(conf, defaults, config);
		
		try{
			this.db.loadSchema();
			var result = this.db.getSchemaResult(), tables=result.tables, i=0, tab;
			for(;(tab=tables[i++]);) if(tab.name==conf.tableName) break;
			
			if (tab){ if (tab.sql.toLowerCase()===conf.tableSql) this.dbStatus=true; }
			else this.resetTable(conf);
		}
		catch(e){ this.resetTable(conf);	}		

		//this.db.loadSchema(); var result = this.db.getSchemaResult();log(result)
		/* working async solution
			var conn = new air.SQLConnection();
			conn.addEventListener(air.SQLEvent.OPEN, function() {
				conn.addEventListener(air.SQLEvent.SCHEMA, function() {
					var result = conn.getSchemaResult();
					console.info(result);
				});
				conn.loadSchema();
			});
			conn.openAsync(air.File.applicationDirectory.resolvePath('data/test.sqlite'));		
		*/
	}	

	
}})();
